﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DA
{
     public class baocaotongkethocki_DA: ConnectData
    {
        public DataTable LoadDSTongKetHocKy(string MaHK)
        {
            string sql = "SELECT  distinct row_number() OVER(order by l.MaLop) STT, TENLOP, b.soHS SISO, ISNULL((b.soHS - DD.DSDuoiTB),b.soHS) SOLUONGDAT, CAST(ROUND(Convert(float,((Convert(float,b.soHS) - Convert(float,ISNULL(DD.DSDuoiTB,0)))/Convert(float,b.soHS))),2)*100 AS nvarchar) + ' %'  TYLE FROM LOP l INNER join (select MaLop, COUNT(distinct MAHS) soHS from BANGDIEM where MAHK = '" + MaHK + "' group by MaLop) b on l.MALOP = b.MALOP LEFT JOIN (select distinct l.MALOP, COUNT(duoiTB.MaHS) DSDuoiTB from LOP l INNER join ( select distinct MAHS, MALOP, COUNT(MaHS) MHDuoiTB from BANGDIEM where MAHK = '" + MaHK + "' AND ISNULL(DTB,0) < 5 group by MAHS, MALOP) duoiTB on l.MALOP = duoiTB.MALOP group by l.MALOP) DD on DD.MALOP = l.MALOP";
            return LayDatatable(sql);
        }
    }
}
